﻿using System;
using System.Collections.Generic;
using System.Web;
using System.IO;
using System.Net;
using System.Net.Mail;
using Pub.Class;
using System.Collections;
using System.Linq;
using GTS.MiniBox.Helper;
using GTS.MiniBox.Entity;
using System.Text;

namespace GTS.MiniBox.Helper {
    /// <summary>
    /// 通道效果
    /// </summary>
    public class ChannelEffect {
        /// <summary>
        /// 查看区域通道效果 按天查看
        /// </summary>
        /// <param name="cstcode"></param>
        /// <param name="area"></param>
        /// <param name="channels"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public static string GetCityChannelEffectByDay(int cstcode, string area, string channels, string month) {
            DateTime m = month.ToDateTime();
            int begin = m.Day;
            int end = m.GetLastDayOfMonth().Day;

            string strSql = new SQL()
                .Select("convert(varchar(10),UpdateTime,120) Day", "ChannelID", "Type", "cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate")
                .FromNoLock(ChannelRuleDay._)
                .Where("1=1").Where(new Where()
                    .And(ChannelRuleDay._CstCode, cstcode, Operator.Equal)
                    .And(ChannelRuleDay._CityCode, area, Operator.Equal)
                    .And(ChannelRuleDay._UpdateTime, m.GetFirstDayOfMonth().ToDate() + " 00:00:00.000", Operator.MoreThanOrEqual)
                    .And(ChannelRuleDay._UpdateTime, m.AddDays(35).GetFirstDayOfMonth().ToDate() + " 00:00:00.000", Operator.LessThan)
                    .And(ChannelRuleDay._ChannelID, "(" + channels + ")", Operator.In)
                ).GroupBy("convert(varchar(10),UpdateTime,120)", "ChannelID", "Type").ToString();
            IList<ChannelRuleDay> list = Data.Pool("MiniBox").GetDbDataReader(strSql).ToList<ChannelRuleDay>();
            var dayList = list.Select(p => p.Day).Distinct();
            var channelIDList = list.Select(p => new { p.ChannelID, p.Type }).Distinct();
            var smsList = SMSChannelInfoHelper.SelectListByAll("MiniBox");
            var ivrList = IVRChannelInfoHelper.SelectListByAll("MiniBox");

            StringBuilder xml = new StringBuilder();
            xml.AppendFormat("<graph caption='按天查看金额比' subcaption='{0}-{1}' showNames='1' xAxisName='日期' yAxisName='金额比' hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' numberSuffix='%25' numdivlines='3' numVdivlines='0' yaxisminvalue='0' yaxismaxvalue='100' rotateNames='1' baseFont='宋体' baseFontSize='11' outCnvBaseFont='宋体' outCnvBaseFontSize='12'>", month.Replace("-", ""), month.ToDateTime().GetLastDayOfMonth().ToDate().Replace("-", ""));
            xml.Append("<categories>");
            for (int i = begin; i <= end; i++)
                xml.AppendFormat("<category name='{0}' />", m.AddDays(i - 1).Day);
            xml.Append("</categories>");

            channelIDList.Do(c => {
                int channelID = c.ChannelID.Value;
                int type = c.Type.Value;
                string channelName = "";
                if (type == 1) channelName = smsList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                if (type == 2) channelName = ivrList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                string color = Rand.RndColor(false).ToHex();
                xml.AppendFormat("<dataset seriesName='{1}' color='{0}' anchorBorderColor='{0}' anchorBgColor='{0}'>", color, channelName);

                for (int i = begin; i <= end; i++) {
                    string day = m.AddDays(i - 1).ToDate();
                    var info = list.Where(p => p.ChannelID == channelID && p.Type == type && p.Day == day).FirstOrDefault();
                    xml.AppendFormat("<set value='{0}' color='{1}' />", info.IsNull() || info.ChannelID.IsNull() ? 0 : info.SuccessRate, color);
                }
                xml.Append("</dataset>");
            });
            xml.Append("</graph>");
            return xml.ToString();
        }
        /// <summary>
        /// 查看区域通道效果 按小时查看
        /// </summary>
        /// <param name="cstcode"></param>
        /// <param name="area"></param>
        /// <param name="channels"></param>
        /// <param name="hour"></param>
        /// <returns></returns>
        public static string GetCityChannelEffectByHour(int cstcode, string area, string channels, string hour) {
            DateTime m = hour.ToDateTime();
            int begin = 0;
            int end = 24;

            string strSql = new SQL()
                .Select("cast(datepart(hh,UpdateTime) as varchar(2)) Day", "ChannelID", "Type", "cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate")
                .FromNoLock(ChannelRuleHour._)
                .Where("1=1").Where(new Where()
                    .And(ChannelRuleHour._CstCode, cstcode, Operator.Equal)
                    .And(ChannelRuleHour._CityCode, area, Operator.Equal)
                    .And(ChannelRuleHour._UpdateTime, m.ToDate() + " 00:00:00.000", Operator.MoreThanOrEqual)
                    .And(ChannelRuleHour._UpdateTime, m.AddDays(1).ToDate() + " 00:00:00.000", Operator.LessThan)
                    .And(ChannelRuleHour._ChannelID, "(" + channels + ")", Operator.In)
                ).GroupBy("cast(datepart(hh,UpdateTime) as varchar(2))", "ChannelID", "Type").ToString();
            IList<ChannelRuleHour> list = Data.Pool("MiniBox").GetDbDataReader(strSql).ToList<ChannelRuleHour>();
            var dayList = list.Select(p => p.Day).Distinct();
            var channelIDList = list.Select(p => new { p.ChannelID, p.Type }).Distinct();
            var smsList = SMSChannelInfoHelper.SelectListByAll("MiniBox");
            var ivrList = IVRChannelInfoHelper.SelectListByAll("MiniBox");

            StringBuilder xml = new StringBuilder();
            xml.AppendFormat("<graph caption='按小时查看金额比' subcaption='{0}的0-23小时' showNames='1' xAxisName='时间' yAxisName='金额比' hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' numberSuffix='%25' numdivlines='3' numVdivlines='0' yaxisminvalue='0' yaxismaxvalue='100' rotateNames='1' baseFont='宋体' baseFontSize='11' outCnvBaseFont='宋体' outCnvBaseFontSize='12'>", m.ToDate().Replace("-", ""));
            xml.Append("<categories>");
            for (int i = begin; i < end; i++)
                xml.AppendFormat("<category name='{0}' />", i);
            xml.Append("</categories>");

            channelIDList.Do(c => {
                int channelID = c.ChannelID.Value;
                int type = c.Type.Value;
                string channelName = "";
                if (type == 1) channelName = smsList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                if (type == 2) channelName = ivrList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                string color = Rand.RndColor(false).ToHex();
                xml.AppendFormat("<dataset seriesName='{1}' color='{0}' anchorBorderColor='{0}' anchorBgColor='{0}'>", color, channelName);

                for (int i = begin; i < end; i++) {
                    string day = i.ToString();
                    var info = list.Where(p => p.ChannelID == channelID && p.Type == type && p.Day == day).FirstOrDefault();
                    xml.AppendFormat("<set value='{0}' color='{1}' />", info.IsNull() || info.ChannelID.IsNull() ? 0 : info.SuccessRate, color);
                }
                xml.Append("</dataset>");
            });
            xml.Append("</graph>");
            return xml.ToString();
        }
        /// <summary>
        /// 查看省份通道效果 按天查看
        /// </summary>
        /// <param name="cstcode"></param>
        /// <param name="area"></param>
        /// <param name="channels"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public static string GetPrivenceChannelEffectByDay(int cstcode, int privence, string channels, string month) {
            DateTime m = month.ToDateTime();
            int begin = m.Day;
            int end = m.GetLastDayOfMonth().Day;

            string strSql = new SQL()
                .Select("convert(varchar(10),UpdateTime,120) Day", "ChannelID", "Type", "cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate")
                .FromNoLock(ChannelRuleDay._)
                .InnerJoinNoLock(CityInfo._)
                    .On(CityInfo._CityCode, "=", ChannelRuleDay._CityCode)
                    .AndOn(CityInfo._ProvinceCode, "=", privence.ToString())
                .Where("1=1").Where(new Where()
                    .And(ChannelRuleDay._CstCode, cstcode, Operator.Equal)
                    .And(CityInfo._ProvinceCode, privence, Operator.Equal)
                    .And(ChannelRuleDay._UpdateTime, m.GetFirstDayOfMonth().ToDate() + " 00:00:00.000", Operator.MoreThanOrEqual)
                    .And(ChannelRuleDay._UpdateTime, m.AddDays(35).GetFirstDayOfMonth().ToDate() + " 00:00:00.000", Operator.LessThan)
                    .And(ChannelRuleDay._ChannelID, "(" + channels + ")", Operator.In)
                ).GroupBy("convert(varchar(10),UpdateTime,120)", "ChannelID", "Type").ToString();
            IList<ChannelRuleDay> list = Data.Pool("MiniBox").GetDbDataReader(strSql).ToList<ChannelRuleDay>();
            var dayList = list.Select(p => p.Day).Distinct();
            var channelIDList = list.Select(p => new { p.ChannelID, p.Type }).Distinct();
            var smsList = SMSChannelInfoHelper.SelectListByAll("MiniBox");
            var ivrList = IVRChannelInfoHelper.SelectListByAll("MiniBox");

            StringBuilder xml = new StringBuilder();
            xml.AppendFormat("<graph caption='按天查看金额比' subcaption='{0}-{1}' showNames='1' xAxisName='日期' yAxisName='金额比' hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' numberSuffix='%25' numdivlines='3' numVdivlines='0' yaxisminvalue='0' yaxismaxvalue='100' rotateNames='1' baseFont='宋体' baseFontSize='11' outCnvBaseFont='宋体' outCnvBaseFontSize='12'>", month.Replace("-", ""), month.ToDateTime().GetLastDayOfMonth().ToDate().Replace("-", ""));
            xml.Append("<categories>");
            for (int i = begin; i <= end; i++)
                xml.AppendFormat("<category name='{0}' />", m.AddDays(i - 1).Day);
            xml.Append("</categories>");

            channelIDList.Do(c => {
                int channelID = c.ChannelID.Value;
                int type = c.Type.Value;
                string channelName = "";
                if (type == 1) channelName = smsList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                if (type == 2) channelName = ivrList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                string color = Rand.RndColor(false).ToHex();
                xml.AppendFormat("<dataset seriesName='{1}' color='{0}' anchorBorderColor='{0}' anchorBgColor='{0}'>", color, channelName);

                for (int i = begin; i <= end; i++) {
                    string day = m.AddDays(i - 1).ToDate();
                    var info = list.Where(p => p.ChannelID == channelID && p.Type == type && p.Day == day).FirstOrDefault();
                    xml.AppendFormat("<set value='{0}' color='{1}' />", info.IsNull() || info.ChannelID.IsNull() ? 0 : info.SuccessRate, color);
                }
                xml.Append("</dataset>");
            });
            xml.Append("</graph>");
            return xml.ToString();
        }
        /// <summary>
        /// 查看省份通道效果 按小时查看
        /// </summary>
        /// <param name="cstcode"></param>
        /// <param name="area"></param>
        /// <param name="channels"></param>
        /// <param name="hour"></param>
        /// <returns></returns>
        public static string GetPrivenceChannelEffectByHour(int cstcode, int privence, string channels, string hour) {
            DateTime m = hour.ToDateTime();
            int begin = 0;
            int end = 24;

            string strSql = new SQL()
                .Select("cast(datepart(hh,UpdateTime) as varchar(2)) Day", "ChannelID", "Type", "cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate")
                .FromNoLock(ChannelRuleHour._)
                .InnerJoinNoLock(CityInfo._)
                    .On(CityInfo._CityCode, "=", ChannelRuleHour._CityCode)
                    .AndOn(CityInfo._ProvinceCode, "=", privence.ToString())
                .Where("1=1").Where(new Where()
                    .And(ChannelRuleHour._CstCode, cstcode, Operator.Equal)
                    .And(CityInfo._ProvinceCode, privence, Operator.Equal)
                    .And(ChannelRuleHour._UpdateTime, m.ToDate() + " 00:00:00.000", Operator.MoreThanOrEqual)
                    .And(ChannelRuleHour._UpdateTime, m.AddDays(1).ToDate() + " 00:00:00.000", Operator.LessThan)
                    .And(ChannelRuleHour._ChannelID, "(" + channels + ")", Operator.In)
                ).GroupBy("cast(datepart(hh,UpdateTime) as varchar(2))", "ChannelID", "Type").ToString();
            IList<ChannelRuleHour> list = Data.Pool("MiniBox").GetDbDataReader(strSql).ToList<ChannelRuleHour>();
            var dayList = list.Select(p => p.Day).Distinct();
            var channelIDList = list.Select(p => new { p.ChannelID, p.Type }).Distinct();
            var smsList = SMSChannelInfoHelper.SelectListByAll("MiniBox");
            var ivrList = IVRChannelInfoHelper.SelectListByAll("MiniBox");

            StringBuilder xml = new StringBuilder();
            xml.AppendFormat("<graph caption='按小时查看金额比' subcaption='{0}的0-23小时' showNames='1' xAxisName='时间' yAxisName='金额比' hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' numberSuffix='%25' numdivlines='3' numVdivlines='0' yaxisminvalue='0' yaxismaxvalue='100' rotateNames='1' baseFont='宋体' baseFontSize='11' outCnvBaseFont='宋体' outCnvBaseFontSize='12'>", m.ToDate().Replace("-", ""));
            xml.Append("<categories>");
            for (int i = begin; i < end; i++)
                xml.AppendFormat("<category name='{0}' />", i);
            xml.Append("</categories>");

            channelIDList.Do(c => {
                int channelID = c.ChannelID.Value;
                int type = c.Type.Value;
                string channelName = "";
                if (type == 1) channelName = smsList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                if (type == 2) channelName = ivrList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                string color = Rand.RndColor(false).ToHex();
                xml.AppendFormat("<dataset seriesName='{1}' color='{0}' anchorBorderColor='{0}' anchorBgColor='{0}'>", color, channelName);

                for (int i = begin; i < end; i++) {
                    string day = i.ToString();
                    var info = list.Where(p => p.ChannelID == channelID && p.Type == type && p.Day == day).FirstOrDefault();
                    xml.AppendFormat("<set value='{0}' color='{1}' />", info.IsNull() || info.ChannelID.IsNull() ? 0 : info.SuccessRate, color);
                }
                xml.Append("</dataset>");
            });
            xml.Append("</graph>");
            return xml.ToString();
        }
        /// <summary>
        /// 查看通道效果 按天查看
        /// </summary>
        /// <param name="cstcode"></param>
        /// <param name="area"></param>
        /// <param name="channels"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public static string GetChannelEffectByDay(int cstcode, string channels, string month) {
            DateTime m = month.ToDateTime();
            int begin = m.Day;
            int end = m.GetLastDayOfMonth().Day;

            string strSql = new SQL()
                .Select("convert(varchar(10),UpdateTime,120) Day", "ChannelID", "Type", "cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate")
                .FromNoLock(ChannelRuleDay._)
                .Where("1=1").Where(new Where()
                    .And(ChannelRuleDay._CstCode, cstcode, Operator.Equal)
                    .And(ChannelRuleDay._UpdateTime, m.GetFirstDayOfMonth().ToDate() + " 00:00:00.000", Operator.MoreThanOrEqual)
                    .And(ChannelRuleDay._UpdateTime, m.AddDays(35).GetFirstDayOfMonth().ToDate() + " 00:00:00.000", Operator.LessThan)
                    .And(ChannelRuleDay._ChannelID, "(" + channels + ")", Operator.In)
                ).GroupBy("convert(varchar(10),UpdateTime,120)", "ChannelID", "Type").ToString();
            IList<ChannelRuleDay> list = Data.Pool("MiniBox").GetDbDataReader(strSql).ToList<ChannelRuleDay>();
            var dayList = list.Select(p => p.Day).Distinct();
            var channelIDList = list.Select(p => new { p.ChannelID, p.Type }).Distinct();
            var smsList = SMSChannelInfoHelper.SelectListByAll("MiniBox");
            var ivrList = IVRChannelInfoHelper.SelectListByAll("MiniBox");

            StringBuilder xml = new StringBuilder();
            xml.AppendFormat("<graph caption='按天查看金额比' subcaption='{0}-{1}' showNames='1' xAxisName='日期' yAxisName='金额比' hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' numberSuffix='%25' numdivlines='3' numVdivlines='0' yaxisminvalue='0' yaxismaxvalue='100' rotateNames='1' baseFont='宋体' baseFontSize='11' outCnvBaseFont='宋体' outCnvBaseFontSize='12'>", month.Replace("-", ""), month.ToDateTime().GetLastDayOfMonth().ToDate().Replace("-", ""));
            xml.Append("<categories>");
            for (int i = begin; i <= end; i++)
                xml.AppendFormat("<category name='{0}' />", m.AddDays(i - 1).Day);
            xml.Append("</categories>");

            channelIDList.Do(c => {
                int channelID = c.ChannelID.Value;
                int type = c.Type.Value;
                string channelName = "";
                if (type == 1) channelName = smsList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                if (type == 2) channelName = ivrList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                string color = Rand.RndColor(false).ToHex();
                xml.AppendFormat("<dataset seriesName='{1}' color='{0}' anchorBorderColor='{0}' anchorBgColor='{0}'>", color, channelName);

                for (int i = begin; i <= end; i++) {
                    string day = m.AddDays(i - 1).ToDate();
                    var info = list.Where(p => p.ChannelID == channelID && p.Type == type && p.Day == day).FirstOrDefault();
                    xml.AppendFormat("<set value='{0}' color='{1}' />", info.IsNull() || info.ChannelID.IsNull() ? 0 : info.SuccessRate, color);
                }
                xml.Append("</dataset>");
            });
            xml.Append("</graph>");
            return xml.ToString();
        }
        /// <summary>
        /// 查看通道效果 按小时查看
        /// </summary>
        /// <param name="cstcode"></param>
        /// <param name="area"></param>
        /// <param name="channels"></param>
        /// <param name="hour"></param>
        /// <returns></returns>
        public static string GetChannelEffectByHour(int cstcode, string channels, string hour) {
            DateTime m = hour.ToDateTime();
            int begin = 0;
            int end = 24;

            string strSql = new SQL()
                .Select("cast(datepart(hh,UpdateTime) as varchar(2)) Day", "ChannelID", "Type", "cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate")
                .FromNoLock(ChannelRuleHour._)
                .Where("1=1").Where(new Where()
                    .And(ChannelRuleHour._CstCode, cstcode, Operator.Equal)
                    .And(ChannelRuleHour._UpdateTime, m.ToDate() + " 00:00:00.000", Operator.MoreThanOrEqual)
                    .And(ChannelRuleHour._UpdateTime, m.AddDays(1).ToDate() + " 00:00:00.000", Operator.LessThan)
                    .And(ChannelRuleHour._ChannelID, "(" + channels + ")", Operator.In)
                ).GroupBy("cast(datepart(hh,UpdateTime) as varchar(2))", "ChannelID", "Type").ToString();
            IList<ChannelRuleHour> list = Data.Pool("MiniBox").GetDbDataReader(strSql).ToList<ChannelRuleHour>();
            var dayList = list.Select(p => p.Day).Distinct();
            var channelIDList = list.Select(p => new { p.ChannelID, p.Type }).Distinct();
            var smsList = SMSChannelInfoHelper.SelectListByAll("MiniBox");
            var ivrList = IVRChannelInfoHelper.SelectListByAll("MiniBox");

            StringBuilder xml = new StringBuilder();
            xml.AppendFormat("<graph caption='按小时查看金额比' subcaption='{0}的0-23小时' showNames='1' xAxisName='时间' yAxisName='金额比' hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' numberSuffix='%25' numdivlines='3' numVdivlines='0' yaxisminvalue='0' yaxismaxvalue='100' rotateNames='1' baseFont='宋体' baseFontSize='11' outCnvBaseFont='宋体' outCnvBaseFontSize='12'>", m.ToDate().Replace("-", ""));
            xml.Append("<categories>");
            for (int i = begin; i < end; i++)
                xml.AppendFormat("<category name='{0}' />", i);
            xml.Append("</categories>");

            channelIDList.Do(c => {
                int channelID = c.ChannelID.Value;
                int type = c.Type.Value;
                string channelName = "";
                if (type == 1) channelName = smsList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                if (type == 2) channelName = ivrList.Where(p => p.ChannelID == channelID).FirstOrDefault().ChannelName;
                string color = Rand.RndColor(false).ToHex();
                xml.AppendFormat("<dataset seriesName='{1}' color='{0}' anchorBorderColor='{0}' anchorBgColor='{0}'>", color, channelName);

                for (int i = begin; i < end; i++) {
                    string day = i.ToString();
                    var info = list.Where(p => p.ChannelID == channelID && p.Type == type && p.Day == day).FirstOrDefault();
                    xml.AppendFormat("<set value='{0}' color='{1}' />", info.IsNull() || info.ChannelID.IsNull() ? 0 : info.SuccessRate, color);
                }
                xml.Append("</dataset>");
            });
            xml.Append("</graph>");
            return xml.ToString();
        }
        /// <summary>
        /// 查看通道效果 按天查看
        /// </summary>
        /// <param name="cstcode"></param>
        /// <param name="area"></param>
        /// <param name="channels"></param>
        /// <param name="month"></param>
        /// <returns></returns>
        public static string GetChannelCityEffectByDay(int cstcode, int channelID, int type, string areas, string month) {
            DateTime m = month.ToDateTime();
            int begin = m.Day;
            int end = m.GetLastDayOfMonth().Day;

            string strSql = new SQL()
                .Select("convert(varchar(10),UpdateTime,120) Day", "CityCode", "cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate")
                .FromNoLock(ChannelRuleDay._)
                .Where("1=1").Where(new Where()
                    .And(ChannelRuleDay._CstCode, cstcode, Operator.Equal)
                    .And(ChannelRuleDay._UpdateTime, m.GetFirstDayOfMonth().ToDate() + " 00:00:00.000", Operator.MoreThanOrEqual)
                    .And(ChannelRuleDay._UpdateTime, m.AddDays(35).GetFirstDayOfMonth().ToDate() + " 00:00:00.000", Operator.LessThan)
                    .And(ChannelRuleDay._ChannelID, channelID, Operator.Equal)
                    .And(ChannelRuleDay._Type, type, Operator.Equal)
                    .And(ChannelRuleDay._CityCode, "(" + areas + ")", Operator.In)
                ).GroupBy("convert(varchar(10),UpdateTime,120)", "CityCode").ToString();
            IList<ChannelRuleDay> list = Data.Pool("MiniBox").GetDbDataReader(strSql).ToList<ChannelRuleDay>();
            var dayList = list.Select(p => p.Day).Distinct();
            var cityCodeList = list.Select(p => new { p.CityCode }).Distinct();
            var areaList = CityInfoHelper.SelectListByAll("MiniBox");

            StringBuilder xml = new StringBuilder();
            xml.AppendFormat("<graph caption='按天查看金额比' subcaption='{0}-{1}' showNames='1' xAxisName='日期' yAxisName='金额比' hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' numberSuffix='%25' numdivlines='3' numVdivlines='0' yaxisminvalue='0' yaxismaxvalue='100' rotateNames='1' baseFont='宋体' baseFontSize='11' outCnvBaseFont='宋体' outCnvBaseFontSize='12'>", month.Replace("-", ""), month.ToDateTime().GetLastDayOfMonth().ToDate().Replace("-", ""));
            xml.Append("<categories>");
            for (int i = begin; i <= end; i++) xml.AppendFormat("<category name='{0}' />", m.AddDays(i - 1).Day);
            xml.Append("</categories>");

            cityCodeList.Do(c => {
                string areaName = areaList.Where(p => p.CityCode == c.CityCode).FirstOrDefault().CityName;
                string color = Rand.RndColor(false).ToHex();
                xml.AppendFormat("<dataset seriesName='{1}' color='{0}' anchorBorderColor='{0}' anchorBgColor='{0}'>", color, areaName);

                for (int i = begin; i <= end; i++) {
                    string day = m.AddDays(i - 1).ToDate();
                    var info = list.Where(p => p.CityCode == c.CityCode && p.Day == day).FirstOrDefault();
                    xml.AppendFormat("<set value='{0}' color='{1}' />", info.IsNull() || info.CityCode.IsNull() ? 0 : info.SuccessRate, color);
                }
                xml.Append("</dataset>");
            });
            xml.Append("</graph>");
            return xml.ToString();
        }
        /// <summary>
        /// 查看通道效果 按小时查看
        /// </summary>
        /// <param name="cstcode"></param>
        /// <param name="area"></param>
        /// <param name="channels"></param>
        /// <param name="hour"></param>
        /// <returns></returns>
        public static string GetChannelCityEffectByHour(int cstcode, int channelID, int type, string areas, string hour) {
            DateTime m = hour.ToDateTime();
            int begin = 0;
            int end = 24;

            string strSql = new SQL()
                .Select("cast(datepart(hh,UpdateTime) as varchar(2)) Day", "CityCode", "cast(SUM(SpMoney)*100/cast(SUM(SendMoney) as decimal(18, 2)) as decimal(18, 2)) SuccessRate")
                .FromNoLock(ChannelRuleHour._)
                .Where("1=1").Where(new Where()
                    .And(ChannelRuleHour._CstCode, cstcode, Operator.Equal)
                    .And(ChannelRuleHour._UpdateTime, m.ToDate() + " 00:00:00.000", Operator.MoreThanOrEqual)
                    .And(ChannelRuleHour._UpdateTime, m.AddDays(1).ToDate() + " 00:00:00.000", Operator.LessThan)
                    .And(ChannelRuleHour._ChannelID, channelID, Operator.Equal)
                    .And(ChannelRuleHour._Type, type, Operator.Equal)
                    .And(ChannelRuleHour._CityCode, "(" + areas + ")", Operator.In)
                ).GroupBy("cast(datepart(hh,UpdateTime) as varchar(2))", "CityCode").ToString();
            IList<ChannelRuleHour> list = Data.Pool("MiniBox").GetDbDataReader(strSql).ToList<ChannelRuleHour>();
            var dayList = list.Select(p => p.Day).Distinct();
            var cityCodeList = list.Select(p => new { p.CityCode }).Distinct();
            var areaList = CityInfoHelper.SelectListByAll("MiniBox");

            StringBuilder xml = new StringBuilder();
            xml.AppendFormat("<graph caption='按小时查看金额比' subcaption='{0}的0-23小时' showNames='1' xAxisName='时间' yAxisName='金额比' hovercapbg='FFECAA' hovercapborder='F47E00' formatNumberScale='0' decimalPrecision='2' numberSuffix='%25' numdivlines='3' numVdivlines='0' yaxisminvalue='0' yaxismaxvalue='100' rotateNames='1' baseFont='宋体' baseFontSize='11' outCnvBaseFont='宋体' outCnvBaseFontSize='12'>", m.ToDate().Replace("-", ""));
            xml.Append("<categories>");
            for (int i = begin; i < end; i++) xml.AppendFormat("<category name='{0}' />", i);
            xml.Append("</categories>");

            cityCodeList.Do(c => {
                string areaName = areaList.Where(p => p.CityCode == c.CityCode).FirstOrDefault().CityName;
                string color = Rand.RndColor(false).ToHex();
                xml.AppendFormat("<dataset seriesName='{1}' color='{0}' anchorBorderColor='{0}' anchorBgColor='{0}'>", color, areaName);

                for (int i = begin; i < end; i++) {
                    string day = i.ToString();
                    var info = list.Where(p => p.CityCode == c.CityCode && p.Day == day).FirstOrDefault();
                    xml.AppendFormat("<set value='{0}' color='{1}' />", info.IsNull() || info.CityCode.IsNull() ? 0 : info.SuccessRate, color);
                }
                xml.Append("</dataset>");
            });
            xml.Append("</graph>");
            return xml.ToString();
        }
    }
}
